<?php
ini_set('memory_limit', '-1');
ini_set('max_execution_time', 86400);
              
// Set flag that this is a parent file.
define('_JEXEC', 1);
define('DS', DIRECTORY_SEPARATOR);

if (file_exists(dirname(__FILE__) . '/defines.php')) {
    include_once dirname(__FILE__) . '/defines.php';
}

if (!defined('_JDEFINES')) {
    define('JPATH_BASE', dirname(__FILE__));
    require_once JPATH_BASE.'/includes/defines.php';
}

require_once JPATH_BASE.'/includes/framework.php';

// Instantiate the application.
$app = JFactory::getApplication('site');

// Initialise the application.
$app->initialise();

require_once JPATH_BASE.DS.'administrator'.DS.'components'.DS.'com_wrd'.DS.'classes'.DS.'utilities'.DS.'reportProvider.php';
require_once JPATH_BASE.DS.'administrator'.DS.'components'.DS.'com_wrd'.DS.'classes'.DS.'utilities'.DS.'pointProvider.php';
require_once JPATH_BASE.DS.'administrator'.DS.'components'.DS.'com_wrd'.DS.'classes'.DS.'PHPExcel.php';

// Init excel
$objPHPExcel = new PHPExcel();

// Set properties
$objPHPExcel->getProperties()->setCreator("WMC GROUP")
                             ->setLastModifiedBy("WMC GROUP")
                             ->setTitle("Member Birthday Report")
                             ->setSubject("Member Birthday Report")
                             ->setDescription("RMember Birthday Report")
                             ->setKeywords("Member Birthday Report")
                             ->setCategory("Member Birthday Report");

$conditions = array();
$conditions['dateFrom'] = strval($_GET['df']);
$conditions['dateTo'] = strval($_GET['dt']);
// Create a first sheet, representing sales data
$objPHPExcel->setActiveSheetIndex(0);
$activeSheet = $objPHPExcel->getActiveSheet();
$activeSheet->mergeCells('B1:J1');
$activeSheet->setCellValue('B1', 'Member Birthday Report');
$activeSheet->getStyle('B1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$activeSheet->getStyle('B1')->getFont()->setSize(24);
$activeSheet->getStyle('B1')->getFont()->setBold(true);

/************** FILL HEADER **************/
$activeSheet->setCellValue('A3', 'Date From');
$activeSheet->setCellValue('B3', $conditions['dateFrom']);
$activeSheet->setCellValue('C3', 'Date To');
$activeSheet->setCellValue('D3', $conditions['dateTo']);


$index = 5;
$activeSheet->setCellValue('A'.$index, 'No');
$activeSheet->setCellValue('B'.$index, 'Member ID');
$activeSheet->setCellValue('C'.$index, 'Title');
$activeSheet->setCellValue('D'.$index, 'Name');
$activeSheet->setCellValue('E'.$index, 'Email');
$activeSheet->setCellValue('F'.$index, 'Phone');
$activeSheet->setCellValue('G'.$index, 'Day of birthday');
$activeSheet->setCellValue('H'.$index, 'District');
$activeSheet->setCellValue('I'.$index, 'City');
$activeSheet->setCellValue('J'.$index, 'Nationality');
$cols = array('A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J');
foreach($cols as $item)
{
    if($item == 'A')
    {
        setBackgroundColor($activeSheet, $item, $index, 5);
    }
	else if($item == 'B')
    {
        setBackgroundColor($activeSheet, $item, $index, 15);
    }
	else if($item == 'D')
    {
        setBackgroundColor($activeSheet, $item,$index, 25);
    }
	else if($item == 'E')
    {
        setBackgroundColor($activeSheet, $item,$index, 30);
    }
	else if($item == 'F')
    {
        setBackgroundColor($activeSheet, $item, $index, 15);
    }
	else if($item == 'G')
    {
        setBackgroundColor($activeSheet, $item,$index, 15);
    }
	else {
		setBackgroundColor($activeSheet, $item, $index, 20);
	}
	
}

/******** Query Data ********/  
$db = JFactory::getDbo();
$data = reportProvider::memberbirthday($conditions, $db, intval($_GET['l']));

if(!empty($data))
{
  
    
    /*********** BIND TO EXCEL ***********/
    $index = 6;
    $data = $data['items'];
    $size = count($data);
    
    for($i=0; $i<$size; $i++)
    {
		$activeSheet->setCellValue('A'.$index, $i+1); 
        
        $activeSheet->setCellValue('B'.$index, $data[$i]->member_id);    
        $activeSheet->getStyle('B'.$index)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
        
		$activeSheet->setCellValue('C'.$index, $data[$i]->prefix);
        $activeSheet->setCellValue('D'.$index, $data[$i]->full_name);                
        
		$activeSheet->setCellValue('E'.$index, $data[$i]->p_email);                
        $activeSheet->getStyle('E'.$index)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
        
                     
        $activeSheet->setCellValue('F'.$index,$data[$i]->phone);                
        $activeSheet->getStyle('F'.$index)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        
        $activeSheet->setCellValue('G'.$index, date('d-m-Y',strtotime($data[$i]->dob)));
		$activeSheet->setCellValue('H'.$index, (empty($data[$i]->district) ? $data[$i]->c_district:$data[$i]->district) );
		$activeSheet->setCellValue('I'.$index, (empty($data[$i]->city) ? $data[$i]->c_city : $data[$i]->city));
		$activeSheet->setCellValue('J'.$index, $data[$i]->nationality);
        //$objPHPExcel->getActiveSheet()->getStyle('F'.$index)->getNumberFormat()->setFormatCode("d/m/yyyy");
		$index++;

    }
    unset($data);
}

$fileName = 'member_birthday_report_'.date('m-d-Y');
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save(JPATH_BASE.DS.'tmp'.DS.$fileName.'.xlsx');

$fullPath = JPATH_ROOT.DS.'tmp'.DS.$fileName.'.xlsx';
$fsize = filesize($fullPath);

header('Content-Description: File Transfer');
header('Content-Type: application/octet-stream');
header('Content-Disposition: attachment; filename="'.$fileName.'.xlsx"');
header('Content-Transfer-Encoding: binary');
header('Expires: 0');
header('Cache-Control: must-revalidate, post-check=0, pre-check=0');
header('Pragma: public');
header('Content-Length: ' . $fsize);
ob_clean();
flush();
readfile( $fullPath );
exit;          


function setBackgroundColor(&$activeSheet, $colName, $colIndex, $width=15,$bg="cc99ff")
{
    $fill = $activeSheet->getStyle($colName.$colIndex)->getFill();
    $fill->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
    $fill->getStartColor()->setARGB($bg);  
    $activeSheet->getColumnDimension($colName)->setWidth($width);    
    
    $style = $activeSheet->getStyle($colName.$colIndex);
    $style->getFont()->setSize(12);
    //$style->getFont()->setBold(true);
    $style->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
}
